/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package VLSObjects;

import DBConnection.*;
import java.sql.*;

public class ServiceManager {
    
    public boolean checkService(String serviceName, String size, String serviceID)
    {
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM servicelist WHERE serviceName = ? AND size = ? AND serviceID != ?");

            pstmt.setString(i++, serviceName);
            pstmt.setString(i++, size);
            pstmt.setString(i++, serviceID);
            
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {
                result = true;
            }

            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;

    }
    
    public boolean addService(Service newService)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO servicelist ( serviceName, size, service_amount, available) VALUES(?,?,?,?)");

            //include parameters
            //pstmt.setString(i++, newService.getServiceID());
            pstmt.setString(i++, newService.getServiceName());
            pstmt.setString(i++, newService.getSize());
            pstmt.setInt(i++, newService.getAmount());
            pstmt.setString(i++, newService.getAvailable());

            //execute query
            pstmt.executeUpdate();

            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public boolean editService(Service editService)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
           PreparedStatement pstmt = conn.prepareStatement("UPDATE servicelist SET serviceName = ?, size = ?, service_amount = ?, available = ? WHERE serviceID = ?" );

            //include parameters
            pstmt.setString(i++, editService.getServiceName());
            pstmt.setString(i++, editService.getSize());
            pstmt.setInt(i++, editService.getAmount());
            pstmt.setString(i++, editService.getAvailable());
            pstmt.setString(i++, editService.getServiceID());
            

            //execute query
            pstmt.executeUpdate();

            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public boolean deleteService(String serviceID)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("DELETE FROM servicelist WHERE serviceID = ?");

            //include parameters
            pstmt.setString(i++, serviceID);

            //execute query
            pstmt.executeUpdate();

            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public String getServiceID(String serviceName)
    {   String serviceID = "null";
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("SELECT serviceID from servicelist WHERE serviceName = ?");

            //include parameters
            pstmt.setString(i++, serviceName);

            //execute query
            ResultSet rs = pstmt.executeQuery();
            
            while(rs.next())
            {
            serviceID = rs.getString(1);
            }
            
            
            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return serviceID;
    }
}
